package repository

import (
	"django_blog_gin/internal/dto"
	"django_blog_gin/internal/model"
	"django_blog_gin/internal/svc"
	"fmt"
	"strconv"
	"strings"
)

type Article struct {
	svcCtx *svc.ServiceContext
}

func NewArticle(svcCtx *svc.ServiceContext) *Article {
	return &Article{svcCtx: svcCtx}
}

func (a *Article) GetArticleList(limit string, offset string) ([]model.BlogArticle, error) {
	articleList := []model.BlogArticle{}
	sql := fmt.Sprintf(`
		SELECT 
			id,
			title,
			LEFT(body, 300) AS body,
			created_time,
			last_mod_time,
			pub_time,
			status,
			comment_status,
			type,
			views,
			article_order,
			show_toc,
			author_id,
			category_id
		FROM %s
		LIMIT %s OFFSET %s
	`, model.BlogArticle{}.TableName(), limit, offset)
	err := a.svcCtx.MysqlDb.Select(&articleList, sql)
	if err != nil {
		return articleList, err
	}
	return articleList, nil
}

// 创建文章
func (a *Article) CreateArticle(req *dto.CreateBlogArticleReq) error {
	var articleId int
	blogSql := fmt.Sprintf(`
		INSERT INTO %s (title, body, pub_time, status, comment_status, type, views, article_order, show_toc, author_id, category_id, created_time, last_mod_time) 
		VALUES (:title, :body, :pub_time, :status, :comment_status, :type, 0, :article_order, :show_toc, :author_id, :category_id, now(), now())
	`, model.BlogArticle{}.TableName())

	// tagSql := fmt.Sprintf(`
	// 	INSERT INTO %s (article_id, tag_id, created_time, last_mod_time)
	// 	VALUES (:article_id, :tag_id, now(), now())
	// `, model.BlogArticleTags{}.TableName())

	// 开启事务
	tx, err := a.svcCtx.MysqlDb.Begin()
	if err != nil {
		return err
	}
	defer func() {
		// 如果出现panic，回滚事务
		if r := recover(); r != nil {
			tx.Rollback()
		}
	}()

	// 执行文章sql
	_, err = a.svcCtx.MysqlDb.NamedExec(blogSql, req)
	if err != nil {
		return err
	}

	// 获取文章id
	err = a.svcCtx.MysqlDb.QueryRow("SELECT LAST_INSERT_ID()").Scan(&articleId)
	if err != nil {
		tx.Rollback()
		return err
	}

	if req.TagIds != nil {
		blogTagsRepo := NewBlogTags(a.svcCtx)
		err = blogTagsRepo.CreateBlogTagsBatch(articleId, req.TagIds)
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	// 提交事务
	err = tx.Commit()
	if err != nil {
		return err
	}

	return nil
}

// 获取文章详情, 并且获取tag名字, 默认排序为文章article_order顺序
func (a *Article) GetArticleWithTagsList(limit string, offset string, order string, orderPrefix string, category string) ([]model.BlogArticleWithTags, error) {
	articleList := []model.BlogArticleWithTags{}
	// p是published发布， d是隐私的仅自己可见
	sql1 := fmt.Sprintf(`
		SELECT 
			a.id,
			a.title,
			LEFT(a.body, 300) AS body,
			a.created_time,
			a.last_mod_time,
			a.pub_time,
			a.status,
			a.comment_status,
			a.type,
			a.views,
			a.article_order,
			a.show_toc,
			a.author_id,
			a.category_id,
			IFNULL(GROUP_CONCAT(t.name), '') AS tags
		FROM %s a
		LEFT JOIN %s at ON a.id = at.article_id
		LEFT JOIN %s t ON at.tag_id = t.id
	`, model.BlogArticle{}.TableName(), model.BlogArticleTags{}.TableName(), model.BlogTag{}.TableName())
	querySql := "WHERE a.status = 'p'"
	if category != "" {
		querySql += fmt.Sprintf(` AND a.category_id = '%s'`, category)
	}
	sql2 := fmt.Sprintf(`
		GROUP BY a.id
		ORDER BY a.%s %s
		LIMIT %s OFFSET %s`, order, orderPrefix, limit, offset)
	sql := sql1 + querySql + sql2
	err := a.svcCtx.MysqlDb.Select(&articleList, sql)
	if err != nil {
		return nil, err
	}
	return articleList, nil
}

// 获取文章详情, 并且获取tag名字, 默认排序为文章article_order顺序
func (a *Article) GetArticleWithTagsListQueryIds(ids []int, authorID int, limit int, offset int, order string, orderPrefix string) ([]model.BlogArticleWithTags, error) {
	articleList := []model.BlogArticleWithTags{}
	var strIds []string
	var status string

	for _, id := range ids {
		strIds = append(strIds, strconv.Itoa(id))
	}

	if authorID > 0 {
		status = "d"
	} else {
		status = "p"
	}

	// p是published发布， d是隐私的仅自己可见
	sql := fmt.Sprintf(`
		SELECT 
			a.id,
			a.title,
			LEFT(a.body, 300) AS body,
			a.created_time,
			a.last_mod_time,
			a.pub_time,
			a.status,
			a.comment_status,
			a.type,
			a.views,
			a.article_order,
			a.show_toc,
			a.author_id,
			a.category_id,
			IFNULL(GROUP_CONCAT(t.name), '') AS tags
		FROM %s a
		LEFT JOIN %s at ON a.id = at.article_id
		LEFT JOIN %s t ON at.tag_id = t.id
		WHERE a.status = '%s' and a.id in (%s)
		GROUP BY a.id
		ORDER BY a.%s %s
		LIMIT %d OFFSET %d
	`, model.BlogArticle{}.TableName(), model.BlogArticleTags{}.TableName(), model.BlogTag{}.TableName(), status, strings.Join(strIds, ","), order, orderPrefix, limit, offset)
	err := a.svcCtx.MysqlDb.Select(&articleList, sql)
	if err != nil {
		return nil, err
	}
	return articleList, nil
}

// 获取文章详情, 并且获取tag名字, 默认排序为文章article_order顺序，仅获取设置为隐私的文章
func (a *Article) GetArticleWithTagsListForPrivate(limit string, offset string, order string, orderPrefix string) ([]model.BlogArticleWithTags, error) {
	articleList := []model.BlogArticleWithTags{}
	// p是published发布， d是隐私的仅自己可见
	sql := fmt.Sprintf(`
		SELECT 
			a.id,
			a.title,
			LEFT(a.body, 300) AS body,
			a.created_time,
			a.last_mod_time,
			a.pub_time,
			a.status,
			a.comment_status,
			a.type,
			a.views,
			a.article_order,
			a.show_toc,
			a.author_id,
			a.category_id,
			IFNULL(GROUP_CONCAT(t.name), '') AS tags
		FROM %s a
		LEFT JOIN %s at ON a.id = at.article_id
		LEFT JOIN %s t ON at.tag_id = t.id
		WHERE a.status = 'd'
		GROUP BY a.id
		ORDER BY a.%s %s
		LIMIT %s OFFSET %s
	`, model.BlogArticle{}.TableName(), model.BlogArticleTags{}.TableName(), model.BlogTag{}.TableName(), order, orderPrefix, limit, offset)
	err := a.svcCtx.MysqlDb.Select(&articleList, sql)
	if err != nil {
		return nil, err
	}
	return articleList, nil
}

// 获取文章详情,并且获取tag的id以及name
func (a *Article) GetArticleWithTagDetail(id int) (model.BlogArticleWithTagDetail, error) {
	articleDetail := model.BlogArticleWithTagDetail{}
	articleSql := fmt.Sprintf(`
			SELECT 
			a.id,
			a.title,
			LEFT(a.body, 300) AS body,
			a.created_time,
			a.last_mod_time,
			a.pub_time,
			a.status,
			a.comment_status,
			a.type,
			a.views,
			a.article_order,
			a.show_toc,
			a.author_id,
			u.username AS author_username,
			a.category_id
		FROM %s a
		LEFT JOIN %s u ON a.author_id = u.id
		WHERE a.id = ?
	`, model.BlogArticle{}.TableName(), model.AccountsBlogUser{}.TableName())

	err := a.svcCtx.MysqlDb.Get(&articleDetail, articleSql, id)
	if err != nil {
		return articleDetail, err
	}

	tagRepo := NewTagRepo(a.svcCtx)

	tagList, err := tagRepo.SearchTagWithId(id)
	if err != nil {
		return articleDetail, err
	}
	// tagList := []model.BlogTag{}
	// tagSql := fmt.Sprintf(`
	// 	SELECT
	// 		id,
	// 		name
	// 	FROM %s
	// 	WHERE id IN (
	// 		SELECT tag_id FROM %s WHERE article_id = %d
	// 	)
	// `, model.BlogTag{}.TableName(), model.BlogArticleTags{}.TableName(), id)
	// err = a.svcCtx.MysqlDb.Select(&tagList, tagSql)
	// if err != nil {
	// 	return articleDetail, err
	// }

	categoryRepo := NewCategoryRepo(a.svcCtx)
	categoryDetail, err := categoryRepo.SearchCategoryWithId(articleDetail.CategoryId)
	if err != nil {
		return articleDetail, err
	}

	// categoryDetail := model.BlogCategory{}
	// CategoryDetailSql := fmt.Sprintf(" SELECT id, name, slug, `index`, parent_category_id FROM %s WHERE id = ?", model.BlogCategory{}.TableName())
	// err = a.svcCtx.MysqlDb.Get(&categoryDetail, CategoryDetailSql, articleDetail.CategoryId)
	// if err != nil {
	// 	return articleDetail, err
	// }
	articleDetail.Tags = tagList
	articleDetail.CategoryDetail = *categoryDetail
	return articleDetail, nil
}

func (a *Article) GetArticleDetail(id int) (model.BlogArticleDetail, error) {
	articleDetail := model.BlogArticleDetail{}
	sql := fmt.Sprintf(`
		SELECT 
			a.id,
			a.title,
			a.body,
			a.created_time,
			a.last_mod_time,
			a.pub_time,
			a.status,
			a.comment_status,
			a.type,
			a.views,
			a.article_order,
			a.show_toc,
			a.author_id,
			u.username AS author_username,
			a.category_id,
			c.name AS category_name,
			IFNULL(GROUP_CONCAT(t.name), '') AS tags
		FROM %s a
		LEFT JOIN %s at ON a.id = at.article_id
		LEFT JOIN %s t ON at.tag_id = t.id
		LEFT JOIN %s u ON a.author_id = u.id
		LEFT JOIN %s c ON a.category_id = c.id
		WHERE a.id = %d
		GROUP BY a.id
	`,
		model.BlogArticle{}.TableName(),
		model.BlogArticleTags{}.TableName(),
		model.BlogTag{}.TableName(),
		model.AccountsBlogUser{}.TableName(),
		model.BlogCategory{}.TableName(),
		id,
	)
	err := a.svcCtx.MysqlDb.Get(&articleDetail, sql)
	if err != nil {
		return articleDetail, err
	}
	return articleDetail, nil
}

func (a *Article) GetArticleTagsList(id int) ([]model.BlogArticleTags, error) {
	articleTagList := []model.BlogArticleTags{}
	sql := fmt.Sprintf(`
		SELECT 
			id,
			article_id,
			tag_id
		FROM %s
		WHERE article_id = %d
	`, model.BlogArticleTags{}.TableName(), id)
	err := a.svcCtx.MysqlDb.Select(&articleTagList, sql)
	if err != nil {
		return articleTagList, err
	}
	return articleTagList, nil
}

func (a *Article) UpdateArticle(req dto.UpdateBlogArticleReq) error {

	_, err := a.svcCtx.MysqlDb.NamedExec(`UPDATE blog_article SET
		title=:title,
		body=:body,
		comment_status=:comment_status,
		pub_time=:pub_time,
		type=:type,
		article_order=:article_order,
		show_toc=:show_toc,
		category_id=:category_id,
		status=:status
		WHERE id=:id`,
		req)
	if err != nil {
		return err
	}
	return nil
}

// 修改aritlce 的tags
func (a *Article) UpdateArticleTags(articleId int, tagIds []int) error {
	// 1. 先获取之前的tags
	oldTagList, err := a.GetTagsList(articleId)
	if err != nil {
		return err
	}
	oldTagids := []int{}
	for _, tag := range oldTagList {
		oldTagids = append(oldTagids, tag.Id)
	}

	blogTagsRepo := NewBlogTags(a.svcCtx)
	// 2.1 先检查是否有删除的tag
	for _, oldTagId := range oldTagids {
		found := false
		for _, newTagId := range tagIds {
			if oldTagId == newTagId {
				found = true
				break
			}
		}
		if !found {
			// 删除tag
			_, err := a.svcCtx.MysqlDb.Exec(`
				DELETE FROM %s WHERE article_id = %d AND tag_id = %d
			`, model.BlogArticleTags{}.TableName(), articleId, oldTagId)
			if err != nil {
				return err
			}
		}
	}
	// 2.2 再检查是否有新增的tag
	for _, newTagId := range tagIds {
		found := false
		for _, oldTagId := range oldTagids {
			if newTagId == oldTagId {
				found = true
				break
			}
		}
		if !found {
			// 新增tag
			err := blogTagsRepo.CreateBlogTags(articleId, newTagId)
			// query := fmt.Sprintf("INSERT INTO %s (article_id, tag_id) VALUES (?, ?)", model.BlogArticleTags{}.TableName())
			// _, err := a.svcCtx.MysqlDb.Exec(query, articleId, newTagId)
			if err != nil {
				return err
			}
		}
	}
	return nil
}

func (a *Article) GetTagsList(articleId int) ([]model.BlogTag, error) {
	tagList := []model.BlogTag{}
	sql := fmt.Sprintf(`
		SELECT 
			id,
			name,
			slug,
			created_time,
			last_mod_time
		FROM %s
		WHERE id IN (
			SELECT tag_id FROM %s WHERE article_id = %d
		)
	`, model.BlogTag{}.TableName(), model.BlogArticleTags{}.TableName(), articleId)

	err := a.svcCtx.MysqlDb.Select(&tagList, sql)
	if err != nil {
		return tagList, err
	}
	return tagList, nil
}

// 获取热门文章前10篇,只需要获取到id和title, 和views数量
func (a *Article) GetHotArticle() ([]model.BlogArticle, error) {
	articleList := []model.BlogArticle{}
	sql := fmt.Sprintf(`
		SELECT 
			id,
			title,
			views
		FROM %s
		WHERE status = 'p'
		ORDER BY views DESC
		LIMIT 10
	`, model.BlogArticle{}.TableName())

	err := a.svcCtx.MysqlDb.Select(&articleList, sql)
	if err != nil {
		return articleList, err
	}
	return articleList, nil
}

// 获取自己的文章列表
func (a *Article) GetMyArticleList(authorID int, limit, offset string) ([]model.BlogArticle, error) {
	articleList := []model.BlogArticle{}
	sql := fmt.Sprintf(`
		SELECT 
			id,
			title,
			body,
			created_time,
			last_mod_time,
			pub_time,
			status,
			comment_status,
			type,
			views,
			article_order,
			show_toc,
			author_id,
			category_id
		FROM %s
		WHERE author_id = %d
		ORDER BY id DESC
		LIMIT %s OFFSET %s
	`, model.BlogArticle{}.TableName(), authorID, limit, offset)

	err := a.svcCtx.MysqlDb.Select(&articleList, sql)
	if err != nil {
		return articleList, err
	}
	return articleList, nil
}
